use master
go

create database GoodsDB
on
(	name='GoodsDB', 
	filename='D:\SQL\GoodsDB.mdf',
	size=5mb,
	maxsize=50mb,
	filegrowth=5mb
)
log on
(	name='GoodsDB_log', 
	filename='D:\SQL\GoodsDB_log.ldf',
	size=5mb,
	maxsize=50mb,
	filegrowth=5mb
)
use GoodsDB
go
create table GoodsType
(
	TypeID int primary key identity(1,1) not null,
	TypeName nvarchar(20) not null
)
create table GoodsInfo
(
	GoodsID int primary key identity(1,1) not null,
	GoodsName nvarchar(20) not null,
	GoodsColor nvarchar(20) not null,
	GoodsBrand nvarchar(20),
	GoodsMoney money not null,
	TypeID int constraint FK_GoodsInfo_TypeID references GoodsType(TypeID)
)
insert into GoodsType values ('服装内衣'),('鞋包数码'),('手机数码')
insert into GoodsInfo values('提花小西装','红色','菲曼琪',300,1),
('百搭短裤','绿色','哥弟',100,1),('无袖背心','白色','阿依莲',700,1),
('低帮休闲鞋','红色','菲曼琪',900,2),('中跟单鞋','绿色','哥弟 ',400,2),
('平底鞋','白色','阿依莲',200,2),('迷你照相机','红色 ','尼康',500 ,3),
('硬盘','黑色','希捷',600,3),('显卡 ','黑色','技嘉',800,3)
--查询价格最贵的商品名称，商品颜色和商品价格，要求使用别名显示列名
select GoodsName 商品名称,GoodsColor 商品颜色,GoodsMoney 商品价格 from GoodsInfo where GoodsMoney = (select MAX(GoodsMoney) from GoodsInfo)
--按商品类型编号分组查询商品最高价格，最低价格和平均价格，要求使用别名显示列名
select TypeID 商品类型,MAX(GoodsMoney)最高价格,MIN(GoodsMoney)最低价格,AVG(GoodsMoney)平均价格 from GoodsInfo group by TypeID
--查询商品信息所有列，要求商品颜色为红色，价格在300~600之间 
select * from GoodsInfo where GoodsColor = '红色' and GoodsMoney >= 300 and GoodsMoney <= 600
